package com.nasarius.bank.scorecard.web.controller.user;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.propertyeditors.CustomCollectionEditor;
import org.springframework.beans.propertyeditors.CustomDateEditor;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.ServletRequestDataBinder;
import org.springframework.web.bind.annotation.InitBinder;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import com.nasarius.bank.scorecard.dao.BankDetailsDAO;
import com.nasarius.bank.scorecard.dao.FactorsDAO;
import com.nasarius.bank.scorecard.dao.SurveyAnswersDAO;
import com.nasarius.bank.scorecard.dao.SurveyDetailsDAO;
import com.nasarius.bank.scorecard.domain.BankDetails;
import com.nasarius.bank.scorecard.domain.Factors;
import com.nasarius.bank.scorecard.domain.SurveyAnswers;
import com.nasarius.bank.scorecard.domain.SurveyDetails;
import com.nasarius.bank.vo.SearchVO;
import com.nasarius.bank.vo.SurveyAnswerVO;
import com.nasarius.bank.vo.SurveyQuestionVO;

@Controller
public class SurveyAnalysisController {
	/** Logger that is available to subclasses */
	protected final Log LOG = LogFactory.getLog(getClass());
	private WritableCellFormat timesBoldUnderline;
	private WritableCellFormat times;	
	@Autowired
	private BankDetailsDAO bankDetailsDAO;
	@Autowired
	private FactorsDAO factorsDAO;
	@Autowired
	private SurveyDetailsDAO surveyDetailsDAO;
	@Autowired
	private SurveyAnswersDAO surveyAnswersDAO;

	public BankDetailsDAO getBankDetailsDAO() {
		return bankDetailsDAO;
	}

	public void setFactorsDAO(FactorsDAO factorsDAO) {
		this.factorsDAO = factorsDAO;
	}

	public void setSurveyDetailsDAO(SurveyDetailsDAO surveyDetailsDAO) {
		this.surveyDetailsDAO = surveyDetailsDAO;
	}

	public void setSurveyAnswersDAO(SurveyAnswersDAO surveyAnswersDAO) {
		this.surveyAnswersDAO = surveyAnswersDAO;
	}

	@InitBinder
	protected void initBinder(ServletRequestDataBinder binder) {
		binder.registerCustomEditor(List.class, new CustomCollectionEditor(
				List.class));
		binder.registerCustomEditor(Date.class, new CustomDateEditor(
				new SimpleDateFormat("dd/MM/yyyy"), true));
	}

	@RequestMapping(value = "/surveyAnalysis/form", method = RequestMethod.GET)
	public void form(SearchVO searchVO, Model model) {
		List<BankDetails> activeBankDetails = bankDetailsDAO
				.selectAllAuthorizedBankDetails(SecurityContextHolder
						.getContext().getAuthentication().getName());
		List<Factors> allFactors = factorsDAO.selectAllFactors();
		List<Factors> allQualitativeFactors = new ArrayList<Factors>();
		for (Factors factor : allFactors) {
			if (factor != null && factor.getFactortype() != null
					&& factor.getFactortype().equalsIgnoreCase("Qualitative")) {
				allQualitativeFactors.add(factor);
			}
		}
		model.addAttribute("activeBankDetails", activeBankDetails);
		model.addAttribute("allFactors", allQualitativeFactors);
	}

	@ModelAttribute
	public SearchVO newRequest(@RequestParam(required = false) String searchId,
			Model model) {
		SearchVO searchVO = new SearchVO();
		return searchVO;
	}

	@RequestMapping(value = "/surveyAnalysis/result", method = RequestMethod.GET)
	public void result(SearchVO searchVO, HttpServletRequest r,
			HttpServletResponse response) {
		HashMap<Date, Date> factorTimeMap = new HashMap<Date, Date>();
		File file = new File("SurveyResults.xls");
		WorkbookSettings wbSettings = new WorkbookSettings();
		wbSettings.setLocale(new Locale("en", "EN"));
		WritableWorkbook workbook;
		try {
			SimpleDateFormat s = new SimpleDateFormat("dd/MM/yyyy");
			workbook = Workbook.createWorkbook(file, wbSettings);
			workbook.createSheet("SurveyResults", 0);
			WritableSheet excelSheet = workbook.getSheet(0);
			createLabel(excelSheet);
			List<SurveyDetails> surveyDetailsList = formSurveyDetailsList(
					searchVO, factorTimeMap);
			int i = 0;
			for (SurveyDetails surveyDetails : surveyDetailsList) {
				String period = s.format(surveyDetails.getStartdate()) + " - "
						+ s.format(surveyDetails.getEnddate());
				addCaptionForPeriod(excelSheet, 0, i, "Period");
				addCaptionForPeriod(excelSheet, 1, i, period);
				i = i + 2;
				List<SurveyQuestionVO> surveyQuestionList = formSurveyResults(
						surveyDetails.getSurveyid(), searchVO.getFactorsIds());
				for (SurveyQuestionVO surveyQuestionVO : surveyQuestionList) {
					addCaption(excelSheet, 0, i, surveyQuestionVO.getQuestion());
					addCaption(excelSheet, 1, i, surveyQuestionVO.getFactor());
					int columnInc = 2;
					List<SurveyAnswerVO> surveyAnswerList = surveyQuestionVO
							.getSurveyAnswerList();
					for (int j = 0; j < surveyAnswerList.size(); j++) {
						SurveyAnswerVO surveyAnswerVO = surveyAnswerList.get(j);
						addCaption(excelSheet, columnInc, i, surveyAnswerVO
								.getAnswer());
						columnInc = columnInc + 1;
					}
					i = i + 1;
				}
				i = i + 1;
			}
			workbook.write();
			workbook.close();
			ServletOutputStream sos = null;
			sos = response.getOutputStream();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=SurveyResults.xls");
			sos.write(FileUtils.readFileToByteArray(file));
			sos.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	private List<SurveyQuestionVO> formSurveyResults(Integer surveyid,
			List<String> factorIds) {
		List<SurveyQuestionVO> surveyQuestionList = new ArrayList<SurveyQuestionVO>();
		SurveyQuestionVO surveyQuestionVO = new SurveyQuestionVO();
		surveyQuestionVO.setQuestion("Question");
		surveyQuestionVO.setFactor("Factor");
		List<SurveyAnswerVO> userNameList = new ArrayList<SurveyAnswerVO>();
		List<SurveyAnswers> userDetailsList = surveyAnswersDAO
				.surveyUserNameByFactor(surveyid, factorIds);
		for (SurveyAnswers surveyAnswers : userDetailsList) {
			SurveyAnswerVO surveyAnswerVO = new SurveyAnswerVO();
			surveyAnswerVO.setAnswer(surveyAnswers.getUsername());
			userNameList.add(surveyAnswerVO);
		}
		surveyQuestionVO.setSurveyAnswerList(userNameList);
		surveyQuestionList.add(surveyQuestionVO);
		List<SurveyAnswerVO> surveyAnswerList = new ArrayList<SurveyAnswerVO>();
		List<SurveyAnswers> surveyAnswersList = surveyAnswersDAO
				.surveyQuestionsByFactor(surveyid, factorIds);
		String questionid = "";
		SurveyQuestionVO surveyQuestionsVO = new SurveyQuestionVO();
		for (SurveyAnswers surveyAnswers : surveyAnswersList) {
			String currentQuestionID = surveyAnswers.getSurveyquestionid();
			if (!questionid.equalsIgnoreCase(currentQuestionID)) {
				questionid = currentQuestionID;
				if (surveyAnswerList.size() > 0) {
					surveyQuestionsVO.setSurveyAnswerList(surveyAnswerList);
					surveyQuestionList.add(surveyQuestionsVO);
				}
				surveyQuestionsVO = new SurveyQuestionVO();
				surveyQuestionsVO.setQuestion(surveyAnswers.getQuestionDesc());
				surveyQuestionsVO.setFactor(surveyAnswers.getFactorName());
				surveyAnswerList = new ArrayList<SurveyAnswerVO>();
			}
			SurveyAnswerVO surveyAnswerVO = new SurveyAnswerVO();
			surveyAnswerVO.setAnswer(surveyAnswers.getAnswercode());
			if(StringUtils.equals("0", surveyAnswers.getAnswercode())){
				surveyAnswerVO.setAnswer("NA");
			}
			
			surveyAnswerList.add(surveyAnswerVO);
		}
		if (surveyAnswerList.size() > 0) {
			surveyQuestionsVO.setSurveyAnswerList(surveyAnswerList);
			surveyQuestionList.add(surveyQuestionsVO);
		}
		return surveyQuestionList;
	}

	private List<SurveyDetails> formSurveyDetailsList(SearchVO searchVO,
			HashMap<Date, Date> factorTimeMap) {
		LOG.debug("fromDate = " + searchVO.getFromDt());
		LOG.debug("toDate = " + searchVO.getToDt());
		Date fromDate = searchVO.getFromDt();
		Date toDate = searchVO.getToDt();
		List<String> bankIds = searchVO.getBankIds();
		List<SurveyDetails> periodList = surveyDetailsDAO
				.surveyDetailsByBankCode(bankIds, fromDate, toDate);
		return periodList;
	}

	private void addCaption(WritableSheet sheet, int column, int row, String s)
			throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, s, timesBoldUnderline);
		sheet.addCell(label);
	}

	private void addCaptionForPeriod(WritableSheet sheet, int column, int row, String s)
			throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, s, times);
		sheet.addCell(label);
	}
	private void createLabel(WritableSheet sheet) throws WriteException {
		sheet.getSettings().setDefaultColumnWidth(25);

		// Create create a bold font with unterlines
		WritableFont times10ptBoldUnderline = new WritableFont(
				WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE);
		timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
		// Lets automatically wrap the cells
		timesBoldUnderline.setWrap(true);
		timesBoldUnderline.setBorder(Border.ALL, BorderLineStyle.THIN);
		timesBoldUnderline.setVerticalAlignment(VerticalAlignment.TOP);
		// Create create a bold font with unterlines
		WritableFont times10ptBold = new WritableFont(
				WritableFont.ARIAL, 10, WritableFont.BOLD, false,
				UnderlineStyle.NO_UNDERLINE);
		times = new WritableCellFormat(times10ptBold);
		times.setWrap(true);
		times.setVerticalAlignment(VerticalAlignment.TOP);
		CellView cv = new CellView();
		cv.setFormat(times);
		cv.setFormat(timesBoldUnderline);
		cv.setAutosize(true);
	}
}
